import ExcelJS from 'exceljs';
// 根据列的坐标展示excel的英文版字母
const getFileExtension = (fileName: string) => {
  const parts = fileName.split('.');
  if (parts.length > 1) {
    return parts[parts.length - 1].toLowerCase();
  } else {
    return '';
  }
};
export function getExcelColumn(number: number | string) {
  if (typeof number !== 'number') {
    number = parseInt(number);
  }
  let column = '';
  while (number >= 0) {
    const remainder = number % 26;
    column = String.fromCharCode(remainder + 65) + column;
    number = Math.floor(number / 26) - 1;
    if (number < 0) break;
  }
  return column;
}
// 根据列的英文名称获取列的坐标
export function getExcelColumnIndex(column: string) {
  let index = 0;
  for (let i = 0; i < column.length; i++) {
    const charCode = column.charCodeAt(i) - 64;
    index = index * 26 + charCode;
  }
  return index - 1;
}
// 根据excel的字母获取范围
export function getRangeFromExcelLetter(letter: string) {
  const pattern = /^([A-Z]+)([0-9]+)(:([A-Z]+)([0-9]+))?$/;
  const match = letter.match(pattern);
  if (!match) {
    throw new Error('Invalid range format');
  }
  const startColumn = getExcelColumnIndex(match[1]);
  const startRow = parseInt(match[2]) - 1;
  const endColumn = match[4] ? getExcelColumnIndex(match[4]) : startColumn;
  const endRow = match[5] ? parseInt(match[5]) - 1 : startRow;
  return {
    startColumn,
    startRow,
    endColumn,
    endRow,
  };
}
/**
 * 根据范围字符串 0,0,10,10 获取位置数组
 * @param position 范围字符串 0,0,10,10
 * @returns 位置数组 ['A1', 'A2']
 */
export function getLetterWordArrFromPosition(position: string) {
  const [startRow, startColumn, endRow, endColumn] = position.split(',').map(Number);
  const letterWordArr: string[] = [];
  for (let i = startRow; i <= endRow; i++) {
    for (let j = startColumn; j <= endColumn; j++) {
      const letter = getExcelColumn(j);
      letterWordArr.push(`${letter}${i + 1}`);
    }
  }
  return letterWordArr;
}
/**
 * 计算excel位置
 * @param position
 * @param param1
 * @returns
 */
export function calcExcelPosition(position: any, { row, col }: any) {
  let [startRow, startColumn] = position.split(',');
  startRow = parseInt(startRow) + parseInt(row) + 1;
  startColumn = getExcelColumn(parseInt(startColumn) + parseInt(col));
  return `${startColumn}${startRow}`;
}
export function getMergedRegions(cells: string[]) {
  // 解析单元格坐标为行和列索引
  function parseCell(cell: string) {
    const match = cell.match(/^([A-Za-z]+)(\d+)$/);
    if (!match) throw new Error('Invalid cell format');
    const letters = match[1];
    const number = parseInt(match[2], 10) - 1; // 行号转换为0-based

    // 将列字母转换为0-based数字（A=0, B=1, ..., Z=25, AA=26等）
    let column = getExcelColumnIndex(letters);

    return { row: number, column };
  }

  // 合并连续的列区间
  function mergeColumns(columns: any[]) {
    if (columns.length === 0) return [];
    columns.sort((a, b) => a - b);
    const merged = [];
    let currentStart = columns[0];
    let currentEnd = columns[0];

    for (let i = 1; i < columns.length; i++) {
      if (columns[i] === currentEnd + 1) {
        currentEnd = columns[i];
      } else {
        merged.push({ start: currentStart, end: currentEnd });
        currentStart = currentEnd = columns[i];
      }
    }
    merged.push({ start: currentStart, end: currentEnd });
    return merged;
  }

  // 合并相邻的矩形区域
  function mergeAdjacentRegions(regions: any) {
    regions.sort((a: any, b: any) => a.startRow - b.startRow || a.startColumn - b.startColumn);
    const merged = [];
    for (const region of regions) {
      if (merged.length === 0) {
        merged.push(region);
      } else {
        const last = merged[merged.length - 1];
        // 检查行是否连续且列有重叠/相邻
        if (
          region.startRow === last.endRow + 1 &&
          !(region.endColumn < last.startColumn - 1 || region.startColumn > last.endColumn + 1)
        ) {
          // 扩展现有区域
          last.endRow = region.endRow;
          last.startColumn = Math.min(last.startColumn, region.startColumn);
          last.endColumn = Math.max(last.endColumn, region.endColumn);
        } else {
          merged.push(region);
        }
      }
    }
    return merged;
  }

  // 主处理流程
  const parsedCells = cells.map((cell) => parseCell(cell));

  // 按行分组并合并列区间
  const rows: any = {};
  parsedCells.forEach((cell) => {
    if (!rows[cell.row]) rows[cell.row] = [];
    rows[cell.row].push(cell.column);
  });

  const rowIntervals = Object.keys(rows)
    .map((row) => ({
      row: parseInt(row, 10),
      intervals: mergeColumns(rows[row]),
    }))
    .sort((a, b) => a.row - b.row);

  // 初始化合并区域
  let mergedRegions: any = [];
  rowIntervals.forEach(({ row, intervals }) => {
    intervals.forEach((interval) => {
      let merged = false;
      for (let i = 0; i < mergedRegions.length; i++) {
        const region = mergedRegions[i];
        // 检查行是否连续且列有重叠/相邻
        if (
          row === region.endRow + 1 &&
          !(interval.end < region.startColumn - 1 || interval.start > region.endColumn + 1)
        ) {
          // 扩展区域
          region.endRow = row;
          region.startColumn = Math.min(region.startColumn, interval.start);
          region.endColumn = Math.max(region.endColumn, interval.end);
          merged = true;
          break;
        }
      }
      if (!merged) {
        mergedRegions.push({
          startRow: row,
          endRow: row,
          startColumn: interval.start,
          endColumn: interval.end,
        });
      }
    });
  });

  // 最终合并相邻区域
  let previousLength;
  do {
    previousLength = mergedRegions.length;
    mergedRegions = mergeAdjacentRegions(mergedRegions);
  } while (mergedRegions.length < previousLength);

  return mergedRegions;
}

export async function loadExcelFile(file: File) {
  const workbook: any = new ExcelJS.Workbook();
  const buffer = await file.arrayBuffer();
  const pending = workbook[getFileExtension(file.name) as any]?.load(buffer);
  if (pending) {
    await pending;
  } else {
    throw new Error('Unsupported file type');
  }
  return workbook;
}
